package com.wxgy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wxgy.dao.DBConn;
import com.wxgy.dao.IUserDao;
import com.wxgy.entity.User;

public class UserDaoImpl implements IUserDao{
	Connection conn = null;
	PreparedStatement pstm=null;
	ResultSet rs = null;
//	int pageCount;
	int rowCount;
//	int pageSize=5;
	public boolean  queryUser(User user) {
		boolean flag=false;
		try {
			conn=new DBConn().getConn();
			String sql="select count(*)  from user where username=? and passwd=?";
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, user.getUserName());
			pstm.setString(2,user.getPasswd());
			rs=pstm.executeQuery();
			if(rs.next()) {
				int  count=rs.getInt(1);
				if(count>0)
				  flag=true;
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();
		}
		
		return flag;
	}
	public User queryById(int userId) {
		User user=null;
		try {
			conn=new DBConn().getConn();
			String sql="select *   from user where userid=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, userId);
			rs=pstm.executeQuery();
			if(rs.next()) {
				user=new User();
				user.setUserId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPasswd(rs.getString(3));
				user.setGrade(rs.getInt(4));
				
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();
		}
		
		return user;
	}
	public int getRowCount() {   
		try {
			conn = new DBConn().getConn();
			String sql="select count(*) from user";
			pstm=conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			rowCount = rs.getInt(1);
	} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return rowCount;	
		}
	public List<User> queryUserByid(int userId){
		User user=null;
		List<User> list=new ArrayList<>();
		try {
			conn=new DBConn().getConn();
			String sql="select * from user where userid=?";
			pstm=conn.prepareStatement(sql);
			pstm.setInt(1, userId);
			rs=pstm.executeQuery();
			while(rs.next()) {
				user=new User();
				user.setUserId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPasswd(rs.getString(3));
				user.setGrade(rs.getInt(4));
				list.add(user);
		}		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();		}
		return list;
	}
	
	public List<User> queryAllUser(){
		User user=null;
		List<User> list=new ArrayList<>();
		try {
			conn=new DBConn().getConn();
			String sql="select * from user ";
			pstm=conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			while(rs.next()) {
				user=new User();
				user.setUserId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPasswd(rs.getString(3));
				user.setGrade(rs.getInt(4));
				list.add(user);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			this.close();
		}
		
		return list;
	}
	
	public boolean addUser(User user) {
		boolean flag = false;
		try {
			conn = new DBConn().getConn();
			String sql = "insert into user(username,passwd,grade) values(?,?,?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, user.getUserName());
			pstm.setString(2, user.getPasswd());
			pstm.setInt(3, user.getGrade());
			int count = pstm.executeUpdate();
			if (count > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return flag;
	}
	
	public boolean updateUserById(int userId,User user) {
		boolean flag = false;
		try {
			conn = new DBConn().getConn();
			String sql = "update  user set username=?,passwd=?, grade=?  where userid=?";
			pstm = conn.prepareStatement(sql);
			
			pstm.setString(1, user.getUserName());
			pstm.setString(2, user.getPasswd());
			pstm.setInt(3, user.getGrade());
			pstm.setInt(4, userId);
			int count = pstm.executeUpdate();
			if (count > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return flag;
	}
	
	public boolean  deleteUserById(int userId) {
		boolean flag = false;
		try {
			conn = new DBConn().getConn();
			String sql = "delete from  user  where userid=?";
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, userId);
				
			int count = pstm.executeUpdate();
			if (count > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return flag;
	}
	
	public void close() {
		try {
			if(rs!=null) rs.close();
			if(pstm!=null)  pstm.close();
			if(conn!=null)  conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	

}
